/*
	EXEC SP_WAFER_START_REPORT 0
*/
CREATE PROC SP_WAFER_START_REPORT @FAB_PROCESSSTEP_ID_INPUT INT
AS
BEGIN
	
	CREATE TABLE #TOTAL_WIP_TABLE
	(
		PART_NUMBER VARCHAR(50),
		VENDOR_NAME VARCHAR(50),
		LOT_NUMBER VARCHAR(50),
		QTY_IN INT,
		DATE_IN DATETIME,
		IS_REGULAR_INTRANSIT INT,
		PS_TYPE VARCHAR(50),
		PS_TYPE_NAME VARCHAR(50),
		PROCESSSTEP_ID INT,
		IS_WIP_CONSIDERED INT DEFAULT 1	
	)
	CREATE TABLE #FG_INFO_TABLE
	(
		FAB_PART_NUMBER VARCHAR(50),
		FAB_PS_ID INT,
		FG_PS_ID INT,
		CQ_SCH_BKLG_QTY INT, --CURRENT QTR SCHEDULED BACKLOG
		CQ_SHIPPED_QTY INT, --CURRENT QTR SHIPPED QTY
		CQ_FORECAST_QTY INT, --CURRENT QTR FORECAST QTY
		NQ_FORECAST_QTY INT, --NEXT QTR FORECAST QTY
		FG_MIN_LEVEL_QTY INT,
		WAFER_QTY INT,
		DIE_BANK_QTY INT,
		ASSEMBLY_QTY INT,
		TEST_QTY INT,
		CLASSSTORE_QTY INT,
		FG_QTY INT,
		NET_DIE_PER_WAFER INT		
	)
	CREATE TABLE #PROCESSSTEPS_TABLE
	(
		PROCESSSTEP_ID INT
	)
	DECLARE @FAB_PROCESSSTEP_ID INT
	DECLARE @FAB_PART_NUMBER VARCHAR(50)
	DECLARE @FAB_PROCESSSTEPS_CURSOR CURSOR
	DECLARE @FG_PROCESSSTEPS_CURSOR CURSOR
	DECLARE @FG_PROCESSSTEP_ID INT
	DECLARE @CURRENT_QTR INT
	DECLARE @CURRENT_YEAR INT
	DECLARE @FG_PRODUCT_ID INT
	DECLARE @CQ_SCHEDULED_QTY INT
	DECLARE @CQ_SCHEDULED_SHIPPED_QTY INT
	DECLARE @CQ_SHIPPED_QTY INT
	DECLARE @CQ_FORECAST_QTY INT
	DECLARE @NQ_FORECAST_QTY INT
	DECLARE @MIN_LEVEL_QTY INT
	DECLARE @WAFER_QTY INT
	DECLARE @DIE_BANK_QTY INT
	DECLARE @ASSEMBLY_QTY INT
	DECLARE @TEST_QTY INT
	DECLARE @CLASSSTORE_QTY INT
	DECLARE	@FG_QTY INT
	DECLARE @NET_DIE_PER_WAFER INT

	SELECT @CURRENT_QTR = DATEPART( Q, CURRENT_TIMESTAMP )
	SELECT @CURRENT_YEAR = DATEPART( YYYY, CURRENT_TIMESTAMP )
	INSERT INTO #TOTAL_WIP_TABLE
		SELECT PART_NUMBER, VENDOR_NAME, LOT_NUMBER, QTY_IN, DATE_IN, IS_REGULAR_INTRANSIT, PS_TYPE, PS_TYPE_NAME, PROCESSSTEP_ID, 1 FROM FN_LOAD_ALL_WIP(1,NULL,NULL)
	
	IF ( @FAB_PROCESSSTEP_ID_INPUT IS NULL OR @FAB_PROCESSSTEP_ID_INPUT <= 0 )
	 BEGIN
		SET @FAB_PROCESSSTEPS_CURSOR = CURSOR FOR SELECT PS.PROCESSSTEP_ID, P.PART_NUMBER FROM PROCESS_STEP PS, PRODUCT P WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS.PS_TYPE = 'FAB'
	 END
	ELSE
	 BEGIN
		SET @FAB_PROCESSSTEPS_CURSOR = CURSOR FOR SELECT PS.PROCESSSTEP_ID, P.PART_NUMBER FROM PROCESS_STEP PS, PRODUCT P WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS.PS_TYPE = 'FAB' AND PS.PROCESSSTEP_ID = @FAB_PROCESSSTEP_ID_INPUT
	 END
	OPEN @FAB_PROCESSSTEPS_CURSOR 
	FETCH NEXT FROM @FAB_PROCESSSTEPS_CURSOR INTO @FAB_PROCESSSTEP_ID, @FAB_PART_NUMBER
	WHILE @@FETCH_STATUS = 0
	 BEGIN
		SET @FG_PROCESSSTEPS_CURSOR = CURSOR FOR 
					SELECT DISTINCT PROCESSSTEP_ID, PRODUCT_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID ( @FAB_PROCESSSTEP_ID, 1 ) WHERE PS_TYPE_NAME  = 'Finished Goods'--FORWARD FLOW
		OPEN @FG_PROCESSSTEPS_CURSOR 
		FETCH NEXT FROM @FG_PROCESSSTEPS_CURSOR INTO @FG_PROCESSSTEP_ID, @FG_PRODUCT_ID
		WHILE @@FETCH_STATUS = 0
		 BEGIN
			SELECT @CQ_SCHEDULED_QTY = SUM(COOL.COOL_QTY) FROM CUSTOMER_OPEN_ORDER_LINEITEM COOL 
			WHERE COOL.PRODUCT_ID = @FG_PRODUCT_ID AND DATEPART(Q,ISNULL(ISNULL(COOL_REVISED_DATE,COOL_SCHEDULE_DATE),COOL_REQUESTED_DATE)) = @CURRENT_QTR
			GROUP BY COOL.PRODUCT_ID
			
			SELECT @CQ_SCHEDULED_SHIPPED_QTY = ISNULL(SUM(CSLIT.CSLIT_QTY_SHIPPED),0) FROM CUSTOMER_OPEN_ORDER_LINEITEM COOL, CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT 
			WHERE CST.CUSTOMERSHIPPING_ID = CSLIT.CUSTOMERSHIPPING_ID AND CSLIT_STATUS = 'SHIPPED' 
			AND CSLIT.LINEITEM_ID = COOL.LINEITEM_ID AND COOL.PRODUCT_ID = @FG_PRODUCT_ID AND DATEPART(Q,ISNULL(ISNULL(COOL_REVISED_DATE,COOL_SCHEDULE_DATE),COOL_REQUESTED_DATE)) = @CURRENT_QTR

			
			SELECT @CQ_SHIPPED_QTY = ISNULL(SUM(CSLIT.CSLIT_QTY_SHIPPED),0) FROM CUSTOMER_OPEN_ORDER_LINEITEM COOL, CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT 
			WHERE CST.CUSTOMERSHIPPING_ID = CSLIT.CUSTOMERSHIPPING_ID AND CSLIT_STATUS = 'SHIPPED' 
			AND CSLIT.LINEITEM_ID = COOL.LINEITEM_ID AND COOL.PRODUCT_ID = @FG_PRODUCT_ID AND DATEPART(Q,CST.CST_SHIP_DATE) = @CURRENT_QTR
			
			IF ( @CURRENT_QTR = 1 )
			 BEGIN
				SELECT @CQ_FORECAST_QTY = SUM( QTY ) FROM FCENTRYMASTER WHERE PRODID = @FG_PRODUCT_ID AND YEAR = @CURRENT_YEAR AND MONTH IN (0, 1, 2)
				SELECT @NQ_FORECAST_QTY = SUM( QTY ) FROM FCENTRYMASTER WHERE PRODID = @FG_PRODUCT_ID AND YEAR = @CURRENT_YEAR AND MONTH IN (3, 4, 5)
			 END
			IF ( @CURRENT_QTR = 2 )
			 BEGIN
				SELECT @CQ_FORECAST_QTY = SUM( QTY ) FROM FCENTRYMASTER WHERE PRODID = @FG_PRODUCT_ID AND YEAR = @CURRENT_YEAR AND MONTH IN (3, 4, 5)
				SELECT @NQ_FORECAST_QTY = SUM( QTY ) FROM FCENTRYMASTER WHERE PRODID = @FG_PRODUCT_ID AND YEAR = @CURRENT_YEAR AND MONTH IN (6, 7, 8)
			 END
			IF ( @CURRENT_QTR = 3 )
			 BEGIN
				SELECT @CQ_FORECAST_QTY = SUM( QTY ) FROM FCENTRYMASTER WHERE PRODID = @FG_PRODUCT_ID AND YEAR = @CURRENT_YEAR AND MONTH IN (6, 7, 8)
				SELECT @NQ_FORECAST_QTY = SUM( QTY ) FROM FCENTRYMASTER WHERE PRODID = @FG_PRODUCT_ID AND YEAR = @CURRENT_YEAR AND MONTH IN (9, 10, 11)
			 END
			IF ( @CURRENT_QTR = 4 )
			 BEGIN
				SELECT @CQ_FORECAST_QTY = SUM( QTY ) FROM FCENTRYMASTER WHERE PRODID = @FG_PRODUCT_ID AND YEAR = @CURRENT_YEAR AND MONTH IN (9, 10, 11)
				SELECT @NQ_FORECAST_QTY = SUM( QTY ) FROM FCENTRYMASTER WHERE PRODID = @FG_PRODUCT_ID AND YEAR = (@CURRENT_YEAR+1) AND MONTH IN (0, 1, 2)
			 END

			IF ( (ISNULL(@CQ_SCHEDULED_QTY,0)-ISNULL(@CQ_SCHEDULED_SHIPPED_QTY,0)) > 0 OR @CQ_FORECAST_QTY > 0 ) --BACKLOG QTY
			 BEGIN				
				SELECT @MIN_LEVEL_QTY = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @FG_PROCESSSTEP_ID AND PARAMETER_KEY = 'MIN LEVEL'
	
				IF ( (SELECT COUNT(*) FROM WIP_FLUSH_TABLE WHERE PROCESSSTEP_ID = @FAB_PROCESSSTEP_ID AND FG_PROCESSSTEP_ID = @FG_PROCESSSTEP_ID ) = 0 )
				 BEGIN
					EXEC SP_CREATE_WIP_FLUSH_SETUP @FAB_PROCESSSTEP_ID
				 END
				SELECT @NET_DIE_PER_WAFER = (GROSS_DIE_FACTOR*FINAL_YIELD)/100 FROM WIP_FLUSH_TABLE WHERE PROCESSSTEP_ID = @FAB_PROCESSSTEP_ID AND FG_PROCESSSTEP_ID = @FG_PROCESSSTEP_ID
				INSERT INTO #PROCESSSTEPS_TABLE
					SELECT DISTINCT PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID ( @FG_PROCESSSTEP_ID, 0 )--BACKWARD FLOW
				--select distinct ps_type_name from process_step
				SELECT @WAFER_QTY = SUM(QTY_IN) FROM #TOTAL_WIP_TABLE TWT, #PROCESSSTEPS_TABLE PST WHERE TWT.PROCESSSTEP_ID = PST.PROCESSSTEP_ID AND ( TWT.PS_TYPE_NAME = 'FAB' OR TWT.PS_TYPE_NAME = 'WAFER STORE' OR TWT.PS_TYPE_NAME = 'SORT' ) AND TWT.IS_WIP_CONSIDERED = 1
				SELECT @DIE_BANK_QTY = SUM(QTY_IN) FROM #TOTAL_WIP_TABLE TWT, #PROCESSSTEPS_TABLE PST WHERE TWT.PROCESSSTEP_ID = PST.PROCESSSTEP_ID AND TWT.PS_TYPE_NAME = 'DIE BANK' AND TWT.IS_WIP_CONSIDERED = 1
				SELECT @ASSEMBLY_QTY = SUM(QTY_IN) FROM #TOTAL_WIP_TABLE TWT, #PROCESSSTEPS_TABLE PST WHERE TWT.PROCESSSTEP_ID = PST.PROCESSSTEP_ID AND TWT.PS_TYPE_NAME = 'ASSEMBLY' AND TWT.IS_WIP_CONSIDERED = 1
				SELECT @TEST_QTY = SUM(QTY_IN) FROM #TOTAL_WIP_TABLE TWT, #PROCESSSTEPS_TABLE PST WHERE TWT.PROCESSSTEP_ID = PST.PROCESSSTEP_ID AND TWT.PS_TYPE_NAME = 'TEST' AND TWT.IS_WIP_CONSIDERED = 1
				SELECT @CLASSSTORE_QTY = SUM(QTY_IN) FROM #TOTAL_WIP_TABLE TWT, #PROCESSSTEPS_TABLE PST WHERE TWT.PROCESSSTEP_ID = PST.PROCESSSTEP_ID AND TWT.PS_TYPE_NAME = 'CLASS STORE' AND TWT.IS_WIP_CONSIDERED = 1
				SELECT @FG_QTY = SUM(QTY_IN) FROM #TOTAL_WIP_TABLE TWT, #PROCESSSTEPS_TABLE PST WHERE TWT.PROCESSSTEP_ID = PST.PROCESSSTEP_ID AND TWT.PS_TYPE_NAME = 'FINISHED GOODS' AND TWT.IS_WIP_CONSIDERED = 1
			
				UPDATE #TOTAL_WIP_TABLE SET IS_WIP_CONSIDERED = 0 WHERE PROCESSSTEP_ID IN (SELECT PROCESSSTEP_ID FROM #PROCESSSTEPS_TABLE)
				DELETE FROM #PROCESSSTEPS_TABLE
				IF ( (SELECT COUNT(*) FROM #FG_INFO_TABLE WHERE FAB_PS_ID = @FAB_PROCESSSTEP_ID) = 0 )
				 BEGIN
					INSERT INTO #FG_INFO_TABLE ( FAB_PART_NUMBER, FAB_PS_ID, FG_PS_ID, CQ_SCH_BKLG_QTY, CQ_SHIPPED_QTY, CQ_FORECAST_QTY, NQ_FORECAST_QTY, FG_MIN_LEVEL_QTY,
							WAFER_QTY, DIE_BANK_QTY, ASSEMBLY_QTY, TEST_QTY, CLASSSTORE_QTY, FG_QTY, NET_DIE_PER_WAFER  )
						VALUES ( @FAB_PART_NUMBER, @FAB_PROCESSSTEP_ID, @FG_PROCESSSTEP_ID, (ISNULL(@CQ_SCHEDULED_QTY,0)-ISNULL(@CQ_SCHEDULED_SHIPPED_QTY,0)), ISNULL(@CQ_SHIPPED_QTY,0), ISNULL(@CQ_FORECAST_QTY,0), ISNULL(@NQ_FORECAST_QTY,0), ISNULL(@MIN_LEVEL_QTY,0),
							ISNULL(@WAFER_QTY,0), ISNULL(@DIE_BANK_QTY,0), ISNULL(@ASSEMBLY_QTY,0), ISNULL(@TEST_QTY,0), ISNULL(@CLASSSTORE_QTY,0), ISNULL(@FG_QTY,0), ISNULL(@NET_DIE_PER_WAFER,1) )
				 END
				ELSE --NEED TO ADD INVENTORY
				 BEGIN
					IF ( (SELECT CQ_SCH_BKLG_QTY FROM #FG_INFO_TABLE WHERE FAB_PS_ID = @FAB_PROCESSSTEP_ID) < (ISNULL(@CQ_SCHEDULED_QTY,0)-ISNULL(@CQ_SCHEDULED_SHIPPED_QTY,0)) ) --MAX BACKLOG QTY
					 BEGIN
						UPDATE #FG_INFO_TABLE SET FG_MIN_LEVEL_QTY = ISNULL(@MIN_LEVEL_QTY,0), NET_DIE_PER_WAFER = @NET_DIE_PER_WAFER WHERE FAB_PS_ID = @FAB_PROCESSSTEP_ID
					 END
					UPDATE #FG_INFO_TABLE SET CQ_SCH_BKLG_QTY = CQ_SCH_BKLG_QTY + (ISNULL(@CQ_SCHEDULED_QTY,0)-ISNULL(@CQ_SCHEDULED_SHIPPED_QTY,0)),
								CQ_SHIPPED_QTY = CQ_SHIPPED_QTY + ISNULL(@CQ_SHIPPED_QTY,0),
								CQ_FORECAST_QTY = CQ_FORECAST_QTY + ISNULL(@CQ_FORECAST_QTY,0),
								NQ_FORECAST_QTY = NQ_FORECAST_QTY + ISNULL(@NQ_FORECAST_QTY,0),
								WAFER_QTY = WAFER_QTY + ISNULL(@WAFER_QTY,0),
								DIE_BANK_QTY = DIE_BANK_QTY + ISNULL(@DIE_BANK_QTY,0),
								ASSEMBLY_QTY = ASSEMBLY_QTY + ISNULL(@ASSEMBLY_QTY,0),
								TEST_QTY = TEST_QTY + ISNULL(@TEST_QTY,0),
								CLASSSTORE_QTY = CLASSSTORE_QTY + ISNULL(@CLASSSTORE_QTY,0),
								FG_QTY = FG_QTY + ISNULL(@FG_QTY ,0)
						WHERE FAB_PS_ID = @FAB_PROCESSSTEP_ID
				 END
			 END					
			SET @CQ_SCHEDULED_QTY = 0
			SET @CQ_SCHEDULED_SHIPPED_QTY = 0
			SET @CQ_SHIPPED_QTY = 0
			SET @CQ_FORECAST_QTY = 0
			SET @NQ_FORECAST_QTY = 0
			SET @MIN_LEVEL_QTY = 0
			SET @FG_PROCESSSTEP_ID = 0
			SET @FG_PRODUCT_ID = 0
			SET @WAFER_QTY = 0
			SET @DIE_BANK_QTY = 0
			SET @ASSEMBLY_QTY = 0
			SET @TEST_QTY = 0
			SET @CLASSSTORE_QTY = 0
			SET @FG_QTY = 0
			FETCH NEXT FROM @FG_PROCESSSTEPS_CURSOR INTO @FG_PROCESSSTEP_ID, @FG_PRODUCT_ID
	 	 END
		CLOSE @FG_PROCESSSTEPS_CURSOR
		DEALLOCATE @FG_PROCESSSTEPS_CURSOR
		SET @FAB_PROCESSSTEP_ID = 0
		SET @FAB_PART_NUMBER = NULL
		FETCH NEXT FROM @FAB_PROCESSSTEPS_CURSOR INTO @FAB_PROCESSSTEP_ID, @FAB_PART_NUMBER
	 END
	CLOSE @FAB_PROCESSSTEPS_CURSOR
	DEALLOCATE @FAB_PROCESSSTEPS_CURSOR
	
/*
	SELECT 	FAB_PART_NUMBER AS [FAB PART NUMBER],
		CQ_SHIPPED_QTY AS [CURRENT QTR SHIPMENTS],
		(CQ_SCH_BKLG_QTY+CQ_SHIPPED_QTY) AS [CURRENT QTR SCHEDULED BKLG + CURR QTR SHIPMENTS],
		CQ_FORECAST_QTY AS [CURR QTR FORECAST],
		(CASE WHEN CQ_FORECAST_QTY > 0 THEN (CQ_SCH_BKLG_QTY+CQ_SHIPPED_QTY)/CQ_FORECAST_QTY ELSE (CQ_SCH_BKLG_QTY+CQ_SHIPPED_QTY) END)AS [CURR QTR SCH BKLG % FILL],
		NQ_FORECAST_QTY AS [NEXT QTR FORECAST],
		WAFER_QTY * NET_DIE_PER_WAFER AS [WAFER DIE INV],
		DIE_BANK_QTY AS [DIE BANK INV],
		ASSEMBLY_QTY AS [ASSEMBLY INV],
		TEST_QTY AS [TEST INV],
		CLASSSTORE_QTY AS [CLASSSTORE INV],
		FG_QTY AS [FG INV],
		((WAFER_QTY * NET_DIE_PER_WAFER)+DIE_BANK_QTY+ASSEMBLY_QTY+TEST_QTY+CLASSSTORE_QTY+FG_QTY) AS [TOTAL INV],
		FG_MIN_LEVEL_QTY AS [MIN LEVEL QTY],
		NET_DIE_PER_WAFER AS [NET DIE PER WAFER]
	FROM #FG_INFO_TABLE
	ORDER BY FAB_PART_NUMBER
*/
	SELECT FAB_PART_NUMBER, CQ_SCH_BKLG_QTY, CQ_SHIPPED_QTY, CQ_FORECAST_QTY, NQ_FORECAST_QTY, FG_MIN_LEVEL_QTY, WAFER_QTY,
		DIE_BANK_QTY, ASSEMBLY_QTY, TEST_QTY, CLASSSTORE_QTY, FG_QTY, FG_MIN_LEVEL_QTY, NET_DIE_PER_WAFER
	FROM  #FG_INFO_TABLE
	ORDER BY FAB_PART_NUMBER
	 
END
